
import pandas as pd
import duckdb

def excel_inner_join(file1, file2, output_file='result.xlsx'):
    # 读取两个Excel文件
    df1 = pd.read_excel(file1)
    df2 = pd.read_excel(file2)
    
    # 创建DuckDB内存数据库
    conn = duckdb.connect(database=':memory:')
    
    # 注册DataFrame为临时表
    conn.register('table1', df1)
    conn.register('table2', df2)
    
    # 执行INNER JOIN查询
    result = conn.execute("""
        SELECT t1.series_no, t1.product_no as product_no1, 
               t2.product_no as product_no2
        FROM table1 t1
        INNER JOIN table2 t2
        ON t1.series_no = t2.series_no
        AND t1.product_no!=t2.product_no       
    """).fetchdf()
    print(result) 

    result2 = conn.execute("""
        SELECT t1.series_no, t2.series_no,t1.product_no as product_no1, 
               t2.product_no as product_no2
        FROM table1 t1
        FULL OUTER JOIN table2 t2
        ON t1.series_no = t2.series_no
        WHERE t1.series_no is null
        OR t2.series_no is null     
    """).fetchdf()
    print(result2)

    # 保存结果到新Excel文件
    result.to_excel(output_file, index=False)
    print(f"结果已保存到 {output_file}")
    return result

# 使用示例
if __name__ == "__main__":
    joined_data = excel_inner_join('/opt/01-apache/python/data/bom1.xlsx', '/opt/01-apache/python/data/bom2.xlsx')
    #print(joined_data.head())
